package org.xawl.news.dao.impl.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.xawl.news.dao.user.UserDao;
import org.xawl.news.entity.role.Role;
import org.xawl.news.entity.user.User;
import org.xawl.news.util.DBUtil;

public class UserDaoImpl implements UserDao {

	@Override
	public User findByUsernameAndPwd(String username, String password) {
		User user = null;
		Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
		try {
			String sql = "select * from t_user where (username=? or email=?) and password=?";
			conn = DBUtil.getConnection();
			// 获取PrepareStatement对象
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, username);
			stmt.setString(2, username);
			stmt.setString(3, password);
			rs = stmt.executeQuery();
			// 遍历结果集
            while (rs.next()) {
               user = new User();
               user.setId(rs.getInt("id"));
			   user.setUsername(rs.getString("username"));
			   user.setPassword(rs.getString("password"));
			   user.setGender(rs.getInt("gender"));
			   user.setMajor(rs.getString("major"));
			   user.setClasses(rs.getString("classes"));
			   user.setGrade(rs.getString("grade"));
			   user.setMobile(rs.getString("mobile"));
			   user.setEmail(rs.getString("email"));
			   user.setIsEnabled(rs.getInt("is_enabled"));
			   user.setRoleId(rs.getInt("role_id"));
			   user.setCreateTime(rs.getTimestamp("create_time"));
			   user.setUpdateTime(rs.getTimestamp("update_time"));
			   user.setLastTime(rs.getTimestamp("last_time"));
			   break;
            }
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBUtil.free(conn, stmt, rs);
		}
		return user;
	}

	@Override
	public boolean save(User user) {
		String sql = "insert into t_user(username, password, gender, major, classes, grade, mobile, email, is_enabled, role_id, last_time,  create_time, update_time) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = {user.getUsername(), user.getPassword(), user.getGender(), user.getMajor(), user.getClasses(), user.getGrade(), user.getMobile(), user.getEmail(), 
				user.getIsEnabled(), user.getRoleId(), user.getLastTime(), user.getCreateTime(), user.getUpdateTime()};
		boolean flag = DBUtil.executeUpdate(sql, params);
		return flag;
	}

	@Override
	public List<User> findList() {
		Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<User> users = new ArrayList<>();
		try {
			String sql = "select * from t_user order by id";
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			// 遍历结果集
            while (rs.next()) {
               User user = new User();
               user.setId(rs.getInt("id"));
			   user.setUsername(rs.getString("username"));
			   user.setPassword(rs.getString("password"));
			   user.setGender(rs.getInt("gender"));
			   user.setMajor(rs.getString("major"));
			   user.setClasses(rs.getString("classes"));
			   user.setGrade(rs.getString("grade"));
			   user.setMobile(rs.getString("mobile"));
			   user.setEmail(rs.getString("email"));
			   user.setIsEnabled(rs.getInt("is_enabled"));
			   user.setRoleId(rs.getInt("role_id"));
			   user.setCreateTime(rs.getTimestamp("create_time"));
			   user.setUpdateTime(rs.getTimestamp("update_time"));
			   user.setLastTime(rs.getTimestamp("last_time"));
			   
			   Role role = DBUtil.getObject(Role.class, "select * from t_role where id=?", user.getRoleId());
			   user.setRole(role);
			   users.add(user);
            }
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DBUtil.free(conn, stmt, rs);
		}
		return users;
	}

	@Override
	public int findCout() {
		String sql = "select count(*) from t_user";
		int count = DBUtil.getCount(sql);
		return count;
	}

}
